package com.mass.word;

import org.springframework.util.StringUtils;
import java.sql.*;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
 
public class CreateSqlWord {
 
    private static String dataBaseName = "demo";
 
    /*public static void main(String[] args) throws Exception {
        List<Map> tableList = getAllTableList();
        FtUtil ftUtil = new FtUtil();
        Map map = new HashMap();
        map.put("tableList", tableList);
        ftUtil.generateFile("/", "word.xml", map, "E:/", "word.doc");
    }
 */
    public static List<Map> getAllTableList(){
        DbConn db = new DbConn();
        Connection con = db.getCon();
 
        List<TableInfo> tables = getAllTable(con);
        List<Map> listTabel = new ArrayList<Map>();
 
        for (TableInfo t : tables ) {
            Map map=new HashMap();
            map.put("tableName",t.getTableName());
            map.put("tableComment",t.getTableComment());
            map.put("table", getTableList(t.getTableName(), con));
            listTabel.add(map);
        }
        db.closeConn();
        return listTabel;
    }
 
    private static List<TableInfo> getAllTable(Connection con) {
        List<TableInfo> list = new ArrayList<TableInfo>();
        String sql="select table_name, TABLE_COMMENT from information_schema.tables where table_schema='"+dataBaseName+"' and table_type='base table';";
        try
        {
            PreparedStatement pp= con.prepareStatement(sql);
            ResultSet rr= pp.executeQuery();
            while(rr.next())
            {
                TableInfo table = new TableInfo();
                table.setTableName(formattUpperCase(rr.getString(1)));
                table.setTableComment(rr.getString(2));
                System.out.println(table.getTableComment()+"（"+table.getTableName()+"）："+formattTableComment(table.getTableComment()));
                list.add(table);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }
 
 
    private static List<Map> getTableList(String tableName , Connection con) {
        List<Map> list = new ArrayList<Map>();
        String sql="" +
                " select ORDINAL_POSITION sort, COLUMN_NAME columnName, COLUMN_COMMENT columnComment, " +
                " IS_NULLABLE isNullable, COLUMN_TYPE columnType, COLUMN_KEY columnKey from information_schema.columns\n " +
                " where table_schema = '"+dataBaseName+"' \n" +
                " and table_name = '"+tableName+"' ; ";
        try
        {
            PreparedStatement pp= con.prepareStatement(sql);
            ResultSet rr= pp.executeQuery();
            while(rr.next())
            {
                Map map=new HashMap();
                map.put("sort", formattData(rr.getString(1)));
                map.put("columnName", formattUpperCase(rr.getString(2)));
                map.put("columnComment", formattData(rr.getString(3)));
                map.put("isNullable", formattData(rr.getString(4)));
                map.put("columnType", formattData(rr.getString(5)));
                map.put("columnKey", formattData(rr.getString(6)));
                list.add(map);
            }
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return list;
    }
 
    private static String formattUpperCase(String column) {
        if(!StringUtils.isEmpty(column)){
            return column.toUpperCase();
        }
        return column;
    }
 
    private static String formattTableComment(String tableComment) {
        if(tableComment.endsWith("表")){
            return tableComment.substring(0,tableComment.length()-1);
        }
        return tableComment;
    }
 
    public static String formattData(String val){
        if(val.isEmpty()){
            return "";
        }
        if(val.contains("\"")){
            val.replaceAll("\"", "'");
        }
        if(val.contains("'")){
            val.replaceAll("'", "'");
        }
        if(val.contains("&")){
            val.replaceAll("&", "&");
        }
        if(val.contains(">")){
            val.replaceAll(">", ">");
        }
        if(val.contains("<")){
            val.replaceAll("<", "<");
        }
        if(val.equals("PRI")){
            val = "主键(PRI)";
        }
        if(val.equals("MUL")){
            val = "";
        }
        return val;
    }
}
